package com.xiaotu.dao;

import com.xiaotu.model.*;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

/**
 * @作者 王艳龙
 * @日期 2021-01-20 9:51
 * @功能
 */
@Mapper
public interface StarTvDao {

    /**
     * 获取艺人信息
     * @param paramModel
     * @return
     */
    @Select("<script>"
            + "select starid AS actor_id,name,array_to_string(\"alias\",',') AS \"alias\","
            + "imagesurl AS image,sex AS gender,birthday AS birth_time,height,weight,school,"
            + "array_to_string(occupation,',') AS main_identity,brokeragefirm AS company,(lastupdatetime::DATE) AS update_time "
            + " from basicinfo.tab_star_info "
            + " where 1=1"
            + "<if test='paramModel.starId !=null and paramModel.starId !=\"\" '>"
            + " and starid in(${paramModel.starId}) "
            + "</if>"
            + "<if test='paramModel.startDate !=null and paramModel.endDate !=null "
            + " and paramModel.startDate !=\"\" and paramModel.endDate !=\"\" '>"
            + " and lastupdatetime::DATE BETWEEN '${paramModel.startDate}' AND '${paramModel.endDate}' "
            + "</if>"
            + " AND name!~*'^[a-z]' ORDER BY starid "
            + "<if test='paramModel.page !=null and paramModel.rows !=null "
            + " and paramModel.page !=\"\" and paramModel.rows !=\"\" '>"
            + " LIMIT #{paramModel.rows} OFFSET #{paramModel.rows}*(#{paramModel.page}-1) "
            + "</if>"
            + "</script>")
    public List<StarModel> getStarInfo(@Param("paramModel") ParamModel paramModel);

    /**
     * 获取作品信息
     * @param paramModel
     * @return
     */
    @Select("<script>"
            + "select tvid AS works_id,tvname AS works_name,array_to_string(\"alias\",',') AS works_alias,tvtype AS type,"
            + " showtype AS themes,array_to_string(platform,',') AS platform,firstplaytime AS publish_time,imageurl AS image,"
            + " array_to_string(actor,',') AS starring,array_to_string(director,',') AS director,(lastupdatetime::DATE) AS update_time, "
            + " array_to_string(screenwriter,',') AS writer,releasetime AS year,array_to_string(sellcompany,',') AS company "
            + "  from basicinfo.tab_tv_info "
            + " where 1=1 "
            + "<if test='paramModel.tvId !=null and paramModel.tvId !=\"\" '>"
            + " and tvid in(${paramModel.tvId}) "
            + "</if>"
            + "<if test='paramModel.startDate !=null and paramModel.endDate !=null "
            + " and paramModel.startDate !=\"\" and paramModel.endDate !=\"\" '>"
            + " and lastupdatetime::DATE BETWEEN '${paramModel.startDate}' AND '${paramModel.endDate}' "
            + "</if>"
            + " and tvtype NOT in(4,5,7) AND tvtype IS NOT NULL ORDER BY tvid "
            + "<if test='paramModel.page !=null and paramModel.rows !=null "
            + " and paramModel.page !=\"\" and paramModel.rows !=\"\" '>"
            + " LIMIT #{paramModel.rows} OFFSET #{paramModel.rows}*(#{paramModel.page}-1) "
            + "</if>"
            + "</script>")
    public List<TvModel> getTvInfo(@Param("paramModel") ParamModel paramModel);


    /**
     * 获取演员、导演、编剧对应的作品信息
     * @param paramModel
     * @return
     */
    @Select("<script>"
            + "select t1.starid AS actor_id,t1.starname AS actor_name,t1.actrole AS role_name,"
            + " t1.tvid AS works_id,t1.tvname AS works_name,(t1.collecttime::DATE) AS update_time "
            + " from basicinfo.${paramModel.tableName} t1 "
            + " LEFT JOIN basicinfo.tab_tv_info t2 ON t1.tvid=t2.tvid "
            + " where 1=1 "
            + "<if test='paramModel.starId !=null and paramModel.starId !=\"\" '>"
            + " and t1.starid in(${paramModel.starId}) "
            + "</if>"
            + "<if test='paramModel.startDate !=null and paramModel.endDate !=null "
            + " and paramModel.startDate !=\"\" and paramModel.endDate !=\"\" '>"
            + " and t1.collecttime::DATE BETWEEN '${paramModel.startDate}' AND '${paramModel.endDate}' "
            + "</if>"
            + " AND t1.starid~*'^[0-9]+$' AND t1.starid IS NOT null AND t1.starid !='' "
            + " AND t1.starname!~*'^[a-z]' AND t2.tvtype NOT in(4,5,7) AND t2.tvtype IS NOT NULL "
            + " ORDER BY t1.starid,t1.tvid "
            + "<if test='paramModel.page !=null and paramModel.rows !=null "
            + " and paramModel.page !=\"\" and paramModel.rows !=\"\" '>"
            + " LIMIT ${paramModel.rows} OFFSET ${paramModel.rows}*(${paramModel.page}-1) "
            + "</if>"
            + "</script>")
    public List<StarWorksModel> getActorWorksInfo(@Param("paramModel") ParamModel paramModel);


    /**
     * 获取艺人获奖信息
     * @param paramModel
     * @return
     */
    @Select("<script>"
            + "select star_id,award_time,award_type,award_section,award_role,award_nominate,(collect_time::DATE) AS update_time "
            + " from basicinfo.tab_actor_award "
            + " where 1=1 "
            + "<if test='paramModel.starId !=null and paramModel.starId !=\"\" '>"
            + " and star_id in(${paramModel.starId}) "
            + "</if>"
            + "<if test='paramModel.startDate !=null and paramModel.endDate !=null "
            + " and paramModel.startDate !=\"\" and paramModel.endDate !=\"\" '>"
            + " and collect_time::DATE BETWEEN '${paramModel.startDate}' AND '${paramModel.endDate}' "
            + "</if>"
            + " ORDER BY star_id,award_type "
            + "<if test='paramModel.page !=null and paramModel.rows !=null "
            + " and paramModel.page !=\"\" and paramModel.rows !=\"\" '>"
            + " LIMIT #{paramModel.rows} OFFSET #{paramModel.rows}*(#{paramModel.page}-1) "
            + "</if>"
            + "</script>")
    public List<StarAwardModel> getActorAwardInfo(@Param("paramModel") ParamModel paramModel);


    /**
     * 获取作品获奖信息
     * @param paramModel
     * @return
     */
    @Select("<script>"
            + "select tv_id,award_time,award_type,award_section,award_role,award_nominate,(collect_time::DATE) AS update_time "
            + " from basicinfo.tab_works_award "
            + " where 1=1 "
            + "<if test='paramModel.tvId !=null and paramModel.tvId !=\"\" '>"
            + " and tv_id in(${paramModel.tvId}) "
            + "</if>"
            + "<if test='paramModel.startDate !=null and paramModel.endDate !=null "
            + " and paramModel.startDate !=\"\" and paramModel.endDate !=\"\" '>"
            + " and collect_time::DATE BETWEEN '${paramModel.startDate}' AND '${paramModel.endDate}' "
            + "</if>"
            + " ORDER BY tv_id,award_type "
            + "<if test='paramModel.page !=null and paramModel.rows !=null "
            + " and paramModel.page !=\"\" and paramModel.rows !=\"\" '>"
            + " LIMIT #{paramModel.rows} OFFSET #{paramModel.rows}*(#{paramModel.page}-1) "
            + "</if>"
            + "</script>")
    public List<TvAwardModel> getWorksAwardInfo(@Param("paramModel") ParamModel paramModel);

    /**
     * 获取艺人小土指数信息
     * @param paramModel
     * @return
     */
    @Select("<script>"
            + "select starid AS star_id,idate as update_time,round(score*10000) AS score "
            + " from stat.tab_star_stat_level "
            + " where 1=1 "
            + "<if test='paramModel.starId !=null and paramModel.starId !=\"\" '>"
            + " and starid in(${paramModel.starId}) "
            + "</if>"
            + "<if test='paramModel.startDate !=null and paramModel.endDate !=null "
            + " and paramModel.startDate !=\"\" and paramModel.endDate !=\"\" '>"
            + " and idate BETWEEN '${paramModel.startDate}' AND '${paramModel.endDate}' "
            + "</if>"
            + " ORDER BY starid,idate "
            + "<if test='paramModel.page !=null and paramModel.rows !=null "
            + " and paramModel.page !=\"\" and paramModel.rows !=\"\" '>"
            + " LIMIT #{paramModel.rows} OFFSET #{paramModel.rows}*(#{paramModel.page}-1) "
            + "</if>"
            + "</script>")
    public List<StarStatLevelModel> getStarStatLevelInfo(@Param("paramModel") ParamModel paramModel);

    /**
     * 获取作品累计播放量、热度信息
     * @param paramModel
     * @return
     */
    @Select("<script>"
            + "select tv_id, "
            + " max(case when platform='腾讯视频' then play_count::BIGINT else 0 end) as tencent, "
            + " max(case when platform='搜狐视频' then play_count::BIGINT else 0 end) as souhu, "
            + " max(case when platform='芒果TV' then play_count::BIGINT else 0 end) as mgTV, "
            + " max(case when platform='爱奇艺' then heat::BIGINT else 0 end) as iqy, "
            + " max(case when platform='优酷' then heat::BIGINT else 0 end) as youku, "
            + " MAX(db_score) AS db_score,collect_time::DATE AS update_time "
            + " from basicinfo.tab_tv_process_data "
            + " where 1=1 "
            + "<if test='paramModel.tvId !=null and paramModel.tvId !=\"\" '>"
            + " and tv_id in(${paramModel.tvId}) "
            + "</if>"
            + "<if test='paramModel.startDate !=null and paramModel.endDate !=null "
            + " and paramModel.startDate !=\"\" and paramModel.endDate !=\"\" '>"
            + " and collect_time::DATE BETWEEN '${paramModel.startDate}' AND '${paramModel.endDate}' "
            + "</if>"
            + " GROUP BY tv_id,collect_time::DATE "
            + "</script>")
    public List<TvPlayCountModel> getTvPlayHeatInfo(@Param("paramModel") ParamModel paramModel);

    /**
     * 校验用户是否存在
     * @param userName
     * @param passWord
     * @return
     */
    @Select(" select count(1) as num from basicinfo.tab_verify_user_info where \"userName\"=#{userName} and \"passWord\"=#{passWord} ")
    public Map<String,Object> getVerifyUser(@Param("userName") String userName,@Param("passWord") String passWord);
    /**
     * 获取播放量总数
     * @return
     */
    @Select(" select count(*) as total from( "
            + " select count(*) AS total FROM basicinfo.tab_tv_process_data GROUP BY tv_id,collect_time::DATE)aa ")
    Map<String,Long> getTvPlayHeatTotal();
    /**
     * 获取艺人总条数
     * @return
     */
    @Select("select count(*) as total from basicinfo.tab_star_info where name!~*'^[a-z]' ")
    Map<String,Long> getStarTotal();

    /**
     * 获取作品总条数
     * @return
     */
    @Select("select count(*) as total from basicinfo.tab_tv_info where tvtype NOT in(4,5,7) AND tvtype IS NOT NULL ")
    Map<String,Long> getTvTotal();

    /**
     * 获取艺人对应作品总条数
     * @return
     */
    @Select("select count(*) as total from ${tableName} t1 "
            + " LEFT JOIN basicinfo.tab_tv_info t2 ON t1.tvid=t2.tvid "
            + " WHERE t1.starid ~* '^[0-9]+$' AND t1.starid IS NOT NULL AND t1.starid != '' AND t1.starname!~*'^[a-z]' "
            + " AND t2.tvtype NOT in(4,5,7) AND t2.tvtype IS NOT NULL")
    Map<String,Long> getWorksTotal(@Param("tableName") String tableName);

    /**
     * 获取总条数
     * @return
     */
    @Select(" select count(*) as total from ${tableName}")
    Map<String,Long> getTotal(@Param("tableName") String tableName);
}
